import openpyxl, os
from support.ui.console import Log
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet._read_only import ReadOnlyWorksheet

class Excel:
    def __init__(self, path, filename, *, RO=False, WO=True):
        self.tag = 'Excel'
        self.filename = os.path.join(path, filename)
        self.isNew = not os.path.exists(self.filename)
        if(self.isNew):
            self.book = openpyxl.Workbook()
            self.mode = 'WR'
            self.book.remove(self.book["Sheet"])
        else:
            self.book = openpyxl.load_workbook(self.filename, read_only=RO)
            self.mode = 'RO'
    
    def __autoCreatePath(self, path):
        if(not os.path.exists(path)):
            os.makedirs(path)

    def save(self, path=None, *, close=True):
        if(path and self.mode == 'WR'):
            self.__autoCreatePath(path)
            self.book.save(path)
        elif(self.mode == 'WR'):
            self.__autoCreatePath(os.path.dirname(self.filename))
            self.book.save(self.filename)
        if(close):
            self.book.close()
    
    def close(self):
        self.book.close()
    
    def __getSheet(self, name):
        try:
            sheet = self.book[name]
        except:
            sheet = self.book.create_sheet(name)
        return sheet
    
    def __cell(self, sheet, row, column):
        if(self.mode == 'RO'):
            cell = sheet._get_cell(row + 1, column + 1)
        else:
            cell = sheet.cell(row, column)
        return cell

    def __write(self, sheet, row, column, *, value=None, font=None):
        cell = self.__cell(sheet, row, column)
        if(font):
            self.__style(cell, font)
        if(isinstance(value, dict)):
            if(value["value"]):
                cell.value = value["value"]
            if(value["datatype"]):
                cell.data_type = value["datatype"]
            if(value["comment"]):
                cell.comment = value["comment"]
        else:
            cell.value = value
        cell.border = Border(left=Side(border_style='thin',color='000000'),right=Side(border_style='thin',color='000000'),top=Side(border_style='thin',color='000000'),bottom=Side(border_style='thin',color='000000'))
    
    def __read(self, sheet, row, column):
        cell = self.__cell(sheet, row, column)
        return {'value':cell.value, 'datatype':cell.data_type, 'comment':cell.comment}
    
    def __del(self, sheet, *, row=None, column=None):
        if(row):
            sheet.delete_rows(row)
        if(column):
            sheet.delete_cols(column)
    
    def __style(self, cell, font=None):
        if(font and self.mode == "WR"):
            name = font.get('name', '微软雅黑')
            size = font.get('size', 10)
            color = font.get('color', '00FFCC99')
            bold = font.get('b', False)
            italic = font.get('i', False)
            cell.font = Font(name = name, size=size, color=color, b=bold, i=italic)
            if(font.get('fill')):
                cell.fill = PatternFill('solid', fgColor=font.get('fill'))
    
    def __dimens(self, sheet, row, column, *, height=None, width=None):
        if(height):
            sheet.row_dimensions[row].height = height
        if(width):
            sheet.column_dimensions[get_column_letter(column)].width = width
    
    def readColumnData(self, sheetname, row):
        arr = []
        sheet = self.__getSheet(sheetname)
        for col in sheet.columns:
            arr.append(col[row].value)
        return arr
    
    def getColumns(self, sheetname, row, names, arr, *, letter=False):
        sheet = self.__getSheet(sheetname)
        for col in sheet.columns:
            if(col[row].value in names):
                if(letter):
                    arr.append(col[row].column_letter)
                else:
                    arr.append(col[row].column - 1)

    def getMaxSize(self, sheetname):
        sheet = self.__getSheet(sheetname)
        return {'row':sheet.max_row, 'column':sheet.max_column}
    
    def writeRowData(self, sheetname, row, column, data, style=None):
        sheet = self.__getSheet(sheetname)
        if(isinstance(data, list)):
            for col in range(len(data)):
                self.__write(sheet, row, column + col, value=data[col], font=style)
        else:
            self.__write(sheet, row, column, value=data, font=style)
    
    def merge(self, sheetname, x1, y1, x2, y2):
        sheet = self.__getSheet(sheetname)
        sheet.merge_cells('{}{}:{}{}'.format(get_column_letter(y1),x1,get_column_letter(y2),x2))
    
    def readDataSpecColumns(self, sheetname, row, columns, arr):
        sheet = self.__getSheet(sheetname)
        for col in columns:
            arr.append(self.__read(sheet, row, col))
        return arr